package com.iraeta.uitl;

import com.iraeta.entity.main.type.ExcelSheetBean;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.List;

public class ExcelUtil {

    public static String ReadData(XSSFSheet excelSheet, int row, int column){
        try{
            Row rowObj = excelSheet.getRow(row);
            Cell cell = rowObj.getCell(column);
            return getCellValue(cell).toString();
        }catch(Exception e){
            return "";
        }
    }

    public static Object getCellValue(Cell cell){
        Object val = "";
        try
        {
            if (isNotNull(cell))
            {
                if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA)
                {
                    val = cell.getNumericCellValue();
                    if (DateUtil.isCellDateFormatted(cell))
                    {
                        val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                    }
                    else
                    {
                        if ((Double) val % 1 != 0)
                        {
                            val = new BigDecimal(val.toString());
                        }
                        else
                        {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                }
                else if (cell.getCellType() == CellType.STRING)
                {
                    val = cell.getStringCellValue();
                }
                else if (cell.getCellType() == CellType.BOOLEAN)
                {
                    val = cell.getBooleanCellValue();
                }
                else if (cell.getCellType() == CellType.ERROR)
                {
                    val = cell.getErrorCellValue();
                }

            }
        }
        catch (Exception e)
        {
            return val;
        }
        return val;
    }

    /**
     * 获取单元格值
     *
     * @param row 获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    public static Object getCellValue(Row row, int column)
    {
        Cell cell = row.getCell(column);
        return getCellValue(cell);
    }

    /**
     * * 判断一个对象是否为空
     *
     * @param object Object
     * @return true：为空 false：非空
     */
    public static boolean isNull(Object object)
    {
        return object == null;
    }

    /**
     * * 判断一个对象是否非空
     *
     * @param object Object
     * @return true：非空 false：空
     */
    public static boolean isNotNull(Object object)
    {
        return !isNull(object);
    }



    /**
     * * 写excel
     *
     * @param filePath 文件路径
     * @param titles  标题
     * @param contents 内容
     */
    public static void writeExcel(String filePath, String sheetName, List<String> titles, List<List<String>> contents) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName); // 创建Sheet时指定Sheet名词
        sheet.setDefaultColumnWidth(30);

        /*写标题*/
        Row row = sheet.createRow(0);  // 创建第1行
        int t_i = 0;
        for (String title : titles) {
            Cell cell = row.createCell(t_i, CellType.STRING); // 创建单元格
            cell.setCellValue(title);
            t_i++;
        }

        /*写内容*/
        for(int c_i = 0; c_i<contents.size(); c_i++){
            List<String> contentLine = contents.get(c_i);
            Row row2 = sheet.createRow(1 + c_i);   // 开始创建第1+n行
            for(int c_j = 0; c_j<contentLine.size(); c_j++){
                Cell cell = row2.createCell(c_j, CellType.STRING); // 创建单元格
                cell.setCellValue(contentLine.get(c_j));
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(filePath); // 如果文件已存在，则覆盖已有文件
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        workbook.close();
    }

    /**
     * * 写excel
     *
     * @param filePath 文件路径
     * @param excelSheetBeanList  sheet数据集合
     */
    public static void writeExcel(String filePath, List<ExcelSheetBean> excelSheetBeanList) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        for (ExcelSheetBean excelSheetBean : excelSheetBeanList) {
            Sheet sheet = workbook.createSheet(excelSheetBean.getSheetName()); // 创建Sheet时指定Sheet名词
            sheet.setDefaultColumnWidth(30);

            /*写标题*/
            Row row = sheet.createRow(0);  // 创建第1行
            int t_i = 0;
            for (String title : excelSheetBean.getTitles()) {
                Cell cell = row.createCell(t_i, CellType.STRING); // 创建单元格
                cell.setCellValue(title);
                t_i++;
            }

            /*写内容*/
            for(int c_i = 0; c_i<excelSheetBean.getContents().size(); c_i++){
                List<String> contentLine = excelSheetBean.getContents().get(c_i);
                Row row2 = sheet.createRow(1 + c_i);   // 开始创建第1+n行
                for(int c_j = 0; c_j<contentLine.size(); c_j++){
                    Cell cell = row2.createCell(c_j, CellType.STRING); // 创建单元格
                    cell.setCellValue(contentLine.get(c_j));
                }
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(filePath); // 如果文件已存在，则覆盖已有文件
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        workbook.close();
    }
}
